set hive.exec.dynamic.partition=true;            --  动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=500;         --  每天生成 60 个分区
set hive.exec.max.dynamic.partitions.pernode=500; --  每天生成 60 个分区
insert overwrite table jms_dm.dm_tab_cn_center_scan_lack_agg_dt partition (dt)
select a.scan_code                                                     as scan_code
     , b.name                                                          as scan_name
     , b.agent_code                                                    as agent_code
     , b.agent_name                                                    as agent_name
     , b.manage_code                                                   as mage_region_code
     , b.manage_name                                                   as mage_region_name
     , a.order_source_code                                             as order_source_code
     , a.order_source_name                                             as order_source_name
     , a.ought_scan_cnt                                                as ought_scan_cnt   --应扫描运单数量
     , a.arrival_lack_cnt                                              as arrival_lack_cnt --卸车到件漏扫数量
     , a.arrival_lack_cnt / a.ought_scan_cnt                           as arrival_lack_rate
     , a.send_lack_cnt                                                 as send_lack_cnt    --装车发件漏扫数量
     , a.send_lack_cnt / a.ought_scan_cnt                              as send_lack_rate
     , (a.arrival_lack_cnt + a.send_lack_cnt) / (a.ought_scan_cnt * 2) as com_lack_rate
     , a.date_time                                                     as date_time
     , a.date_time                                                     as dt

from (
         select next_station_network_code as scan_code
              , max(ordersource_code)     as order_source_code
              , ordersource_name          as order_source_name
              , date_time                 as date_time 
              , count(waybill_no)         as ought_scan_cnt--应扫描运单数量
              , sum(if(have_arr_have_send_flag <> 1 and have_arr_no_send_flag = 1 and have_send_no_arr_flag = 0, 1,
                       0))                as send_lack_cnt--装车发件漏扫漏扫数量
              , sum(if(have_arr_have_send_flag <> 1 and have_send_no_arr_flag = 1 and have_arr_no_send_flag = 0, 1,
                       0))                as arrival_lack_cnt--卸车到件漏扫数量
         from jms_dm.dm_tab_cn_center_scan_lack_total_dt
         where dt = '{{ execution_date | cst_ds }}'
         group by next_station_network_code
                , ordersource_name
                , date_time
     ) a
         left join jms_dim.dim_network_whole_massage b
                   on a.scan_code = b.code
    distribute by dt
;


